import logging
from datetime import date
from typing import Union, Optional, List

from fastapi import APIRouter, Query, Depends, Path
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

mov_head_router = APIRouter(prefix="/movHead", tags=["耗材调拨管理"], dependencies=[Depends(oauth2_scheme)])


class MovSearch(PaginationBase):
    fsiteNo: str = Field(None, description="客户编号")
    ftakedateStart: str = Field(None, description="开始时间")
    ftakedateEnd: str = Field(None, description="结束时间")
    fmovuser: str = Field(None, description="挑拨人员")
    fstocknoOut: str = Field(None, description="调出仓库")
    foutpointNo2: str = Field(None, description="调入仓库")
    fbillno: str = Field(None, description="单号")
    fbillstate: str = Field(None, description="单据状态（1.未调出 2.已调出 8.已作废）")


@mov_head_router.get("/list", summary="耗材调拨查询")
def mov_head_list(mov: MovSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.m_tc_mov_head where 1=1 "
    if mov.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param['site_no'] = mov.fsiteNo
    if mov.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%') "
        param['fbillno'] = mov.fbillno
    if mov.fmovuser is not None:
        sql = sql + " and fmovuser = :fmovuser "
        param['fmovuser'] = mov.fmovuser
    if mov.fstocknoOut is not None:
        sql = sql + " and fstockno_out = :fstocknoOut "
        param['fstocknoOut'] = mov.fstocknoOut
    if mov.foutpointNo2 is not None:
        sql = sql + " and fstockno_in = :foutpointNo2 "
        param['foutpointNo2'] = mov.foutpointNo2
    if mov.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param['fbillstate'] = mov.fbillstate
    if (mov.ftakedateStart is not None) and (mov.ftakedateEnd is not None):
        sql = sql + " and to_char(fmovdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param['ftakedateStart'] = mov.ftakedateStart
        param['ftakedateEnd'] = mov.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fmovuser,fmovdate,fstockno_out,fstockno_in,fitemcount,fpiececount,"
    sql = sql + " ftotalamt,fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate,fbilltype"
    sql = sql + " from lims.m_tc_mov_head where 1=1"
    if mov.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param['site_no'] = mov.fsiteNo
    if mov.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%') "
        param['fbillno'] = mov.fbillno
    if mov.fmovuser is not None:
        sql = sql + " and fmovuser = :fmovuser "
        param['fmovuser'] = mov.fmovuser
    if mov.fstocknoOut is not None:
        sql = sql + " and fstockno_out = :fstocknoOut "
        param['fstocknoOut'] = mov.fstocknoOut
    if mov.foutpointNo2 is not None:
        sql = sql + " and fstockno_in = :foutpointNo2 "
        param['foutpointNo2'] = mov.foutpointNo2
    if mov.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param['fbillstate'] = mov.fbillstate
    if (mov.ftakedateStart is not None) and (mov.ftakedateEnd is not None):
        sql = sql + " and to_char(fmovdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param['ftakedateStart'] = mov.ftakedateStart
        param['ftakedateEnd'] = mov.ftakedateEnd
    sql = sql + " order by  fmovdate,fbillno  desc "
    sql = sql + " limit :limit offset :offset"
    param['offset'] = mov.offset
    param['limit'] = mov.limit
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fmaterialno,b.fbuyprice,b.fqty,b.fprocdate,b.ftotamt,b.fmemo"
    sql_3 = sql_3 + " from lims.m_tc_mov_head a,lims.m_tc_mov_detail b where a.fbillno=b.fbillno "
    if mov.fsiteNo is not None:
        sql_3 = sql_3 + " and a.fsite_no = :site_no "
        param['site_no'] = mov.fsiteNo
    if mov.fbillno is not None:
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%') "
        param['fbillno'] = mov.fbillno
    if mov.fmovuser is not None:
        sql_3 = sql_3 + " and a.fmovuser = :fmovuser "
        param['fmovuser'] = mov.fmovuser
    if mov.fstocknoOut is not None:
        sql_3 = sql_3 + " and fstockno_out = :fstocknoOut "
        param['fstocknoOut'] = mov.fstocknoOut
    if mov.foutpointNo2 is not None:
        sql_3 = sql_3 + " and foutpoint_no2 = :foutpointNo2 "
        param['foutpointNo2'] = mov.foutpointNo2
    if mov.fbillstate is not None:
        sql_3 = sql_3 + " and a.fbillstate = :fbillstate "
        param['fbillstate'] = mov.fbillstate
    if (mov.ftakedateStart is not None) and (mov.ftakedateEnd is not None):
        sql_3 = sql_3 + " and to_char(a.fmovdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param['ftakedateStart'] = mov.ftakedateStart
        param['ftakedateEnd'] = mov.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"

    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fmovuser'] = data[key][2]
        d1['fmovdate'] = data[key][3]
        d1['fstocknoOut'] = data[key][4]
        d1['fstocknoIn'] = data[key][5]
        d1['fitemcount'] = data[key][6]
        d1['fpiececount'] = data[key][7]
        d1['ftotalamt'] = data[key][8]
        d1['fexplanation'] = data[key][9]
        d1['fupddate'] = data[key][10]
        d1['foperator'] = data[key][11]
        d1['fcheckdate'] = data[key][12]
        d1['fcheckuser'] = data[key][13]
        d1['fbillstate'] = data[key][14]
        d1['fbilltype'] = data[key][15]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fmaterialno'] = data_detail[j1][2]
                d2['fbuyprice'] = data_detail[j1][3]
                d2['fqty'] = data_detail[j1][4]
                d2['fprocdate'] = data_detail[j1][5]
                d2['ftotamt'] = data_detail[j1][6]
                d2['fmemo'] = data_detail[j1][7]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": mov.limit, "page": mov.pageIndex,
                 "pages": (count + mov.limit - 1) / mov.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class TransferDetailCreate(BaseModel):
    """耗材调拨明细项的数据模型"""
    fbillno: str = Field(description="单据编号")
    fline: int = Field(description="行号")
    fmaterialno: str = Field(description="耗材编号")
    fbuyprice: Union[str, float] = Field(description="购买价格")
    fqty: Union[str, float] = Field(description="数量")
    ftotamt: Union[str, float] = Field(description="总金额")
    fprocdate: Union[date, str] = Field(description="处理日期")
    fmemo: str = Field(default="", description="备注")
    fmaterialname: Optional[str] = Field(default="", description="耗材名称")


class TransferHeadCreate(BaseModel):
    """耗材调拨头信息的数据模型"""
    fbillno: str = Field(description="单据编号")
    fsiteNo: str = Field(description="站点编号")
    fmovuser: Union[str, int] = Field(description="调拨用户")
    fmovdate: Union[date, str] = Field(description="调拨日期")
    fstocknoOut: str = Field(description="调出仓库")
    foutpointNo2: str = Field(description="调入仓库")
    fexplanation: str = Field(default="", description="说明")
    fitemcount: int = Field(default=0, description="项目计数")
    fpiececount: float = Field(default=0, description="总件数")
    ftotalamt: float = Field(default=0, description="总金额")
    fbillstate: int = Field(default=1, description="单据状态")
    fbilltype: int = Field(default=1, description="单据类型")


class TransferCreate(BaseModel):
    """耗材调拨完整请求的数据模型"""
    fbillno: str = Field(description="单据编号")
    fmovdate: Union[date, str] = Field(description="调拨日期")
    fmovuser: Union[str, int] = Field(description="调拨用户")
    fsiteNo: str = Field(description="站点编号")
    fstocknoOut: str = Field(description="调出仓库")
    foutpointNo2: str = Field(description="调入仓库")
    fexplanation: Optional[str] = Field(default="", description="说明")
    details: List[TransferDetailCreate] = Field(description="调拨明细列表")


@mov_head_router.post("/create", summary="新增耗材挑拨")
def create_mov(transfer: TransferCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    sqllist = []
    fitemcount = 0
    fpiececount = 0
    ftotalamt = 0
    try:
        with db.begin():
            detail_list = []
            d1 = {}
            fbillno = transfer.fbillno
            jsondata = transfer.details
            # 新增明细信息
            for data in jsondata:
                sql = "insert into lims.m_tc_mov_detail(fbillno,fline,fmaterialno,fbuyprice,fqty,ftotamt,fprocdate,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fmaterialno , :fbuyprice , :fqty , :ftotamt , :fprocdate ,'')"

                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fmaterialno": data.fmaterialno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                    "fprocdate": data.fprocdate,
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + data.fqty
                ftotalamt = ftotalamt + data.ftotamt

                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['fmaterialno'] = data.fmaterialno
                d2['fmaterialname'] = data.fmaterialname
                d2['fbuyprice'] = float(data.fbuyprice)
                d2['fqty'] = float(data.fqty)
                d2['ftotamt'] = float(data.ftotamt)
                d2['fmemo'] = ""
                d2['fprocdate'] = data.fprocdate
                detail_list.append(d2)
            # 新增主表
            sql = "insert into lims.m_tc_mov_head(fbillno,fsite_no,fmovuser,fmovdate,fstockno_out,fstockno_in,fitemcount,fpiececount,ftotalamt,fexplanation,fupddate,foperator,fbillstate,fbilltype)"
            sql = sql + " values( :fbillno , :fsiteNo , :fmovuser , :fmovdate , :fstocknoout , :fstocknoin , :fitemcount , :fpiececount , :ftotalamt , :fexplanation ,now(), :fmovuser ,1,1)"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": transfer.fsiteNo,
                "fmovuser": transfer.fmovuser,
                "fmovdate": transfer.fmovdate,
                "fstocknoout": transfer.fstocknoOut,
                "fstocknoin": transfer.foutpointNo2,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "fexplanation": transfer.fexplanation,
            })

            d1['fbillno'] = fbillno
            d1['fsiteno'] = transfer.fsiteNo
            d1['fmovuser'] = transfer.fmovuser
            d1['fmovdate'] = transfer.fmovdate
            d1['fstocknoOut'] = transfer.fstocknoOut
            d1['foutpointNo2'] = transfer.foutpointNo2
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = float(ftotalamt)
            d1['fsrcbillno'] = ""
            d1['fexplanation'] = ""
            d1['fupddate'] = transfer.fmovdate
            d1['foperator'] = transfer.fmovuser
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbillstate'] = 1
            d1['fbilltype'] = 1
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@mov_head_router.post("/update", summary="修改耗材调拨接口")
def update_mov(transfer: TransferCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        with db.begin():
            user_id = EncryptionMiddleware.decrypt_token(token)
            fbillno = transfer.fbillno

            # 删除详情
            sql = "delete from lims.m_tc_mov_detail where fbillno= ：fbillno "
            db.execute(text(sql), {"fbillno": fbillno})

            # 新增详情
            jsondata = transfer.details
            for data in jsondata:
                sql = "insert into lims.m_tc_mov_detail(fbillno,fline,fmaterialno,fbuyprice,fqty,ftotamt,fprocdate,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fmaterialno , :fbuyprice , :fqty , :ftotamt , :fprocdate ,'')"

                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fmaterialno": data.fmaterialno,
                    "fbuyprice": data.fbuyprice,
                    "fqty": data.fqty,
                    "ftotamt": data.ftotamt,
                    "fprocdate": data.fprocdate,
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + data.fqty
                ftotalamt = ftotalamt + data.ftotamt

            # 修改主表
            sql = "update lims.m_tc_mov_head "
            sql = sql + " set fstockno_out= :fstocknoout ,fstockno_in= :fstocknoin ,fexplanation= :fexplanation "
            sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt "
            sql = sql + ",fupddate=now(),foperator= :userid "
            sql = sql + " where fbillno= :fbillno "

            db.execute(text(sql), {
                "fstocknoout": transfer.fstocknoOut,
                "fstocknoin": transfer.foutpointNo2,
                "fexplanation": transfer.fexplanation,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "userid": user_id,
                "fbillno": fbillno,
            })

            return BaseResponse.success()

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@mov_head_router.post("/delete/{fbillno}", summary="删除耗材调拨记录")
def delete_mov_head(fbillno: str = Path(descroption="记录id"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select count(1) from lims.m_tc_mov_head where fbillno= :fbillno  and fbillstate>1"
            count = db.execute(text(sql), {"fbillno": fbillno}).scalar()
            if count > 0:
                return BaseResponse.error(message="该订单不能删除")
            sql = "delete from lims.m_tc_mov_detail where fbillno= :fbillno "
            db.execute(text(sql), {"fbillno": fbillno})
            sql = "delete from lims.m_tc_mov_head where fbillno= :fbillno "
            db.execute(text(sql), {"fbillno": fbillno})
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
